cd "~/Desktop/Senate Pork Replication Files"

use "Senate_Pork.dta",clear

capture log close

log using senate_pork.log, replace

set more off

*Generate election year dummies
tab year, gen(year)
gen elec_yr1=0
replace elec_yr1=1 if year==2002 | year==2003 | year==2004
gen elec_yr2=0
replace elec_yr2=1 if year==2005 | year==2006 | year==2007
gen elec_yr3=0
replace elec_yr3=1 if year==2008 | year==2009 | year==2010
gen elec_yr4=0
replace elec_yr4=1 if year==2011 | year==2012


**************************************************************************************
*Figure 1: CDF Funds (in million pesos) by Senatorial Reelectionist Status, 2001--2012
**************************************************************************************

preserve

replace cdf_targeted=cdf_targeted/1000000

collapse (mean) mean=cdf_targeted (sd) sd=cdf_targeted (count) n=cdf_targeted, by(reelectionist year)
generate hipdaf=mean + invttail(n-1,0.025)*(sd/sqrt(n))
generate lopdaf=mean - invttail(n-1,0.025)*(sd/sqrt(n))

generate year_match=1 if year==2001 & reelectionist==1
replace year_match=2 if year==2001 & reelectionist==0
replace year_match=4 if year==2002 & reelectionist==1
replace year_match=5 if year==2002 & reelectionist==0
replace year_match=7 if year==2003 & reelectionist==1
replace year_match=8 if year==2003 & reelectionist==0
replace year_match=10 if year==2004 & reelectionist==1
replace year_match=11 if year==2004 & reelectionist==0
replace year_match=13 if year==2005 & reelectionist==1
replace year_match=14 if year==2005 & reelectionist==0
replace year_match=16 if year==2006 & reelectionist==1
replace year_match=17 if year==2006 & reelectionist==0
replace year_match=19 if year==2007 & reelectionist==1
replace year_match=20 if year==2007 & reelectionist==0
replace year_match=22 if year==2008 & reelectionist==1
replace year_match=23 if year==2008 & reelectionist==0
replace year_match=25 if year==2009 & reelectionist==1
replace year_match=26 if year==2009 & reelectionist==0
replace year_match=28 if year==2010 & reelectionist==1
replace year_match=29 if year==2010 & reelectionist==0
replace year_match=31 if year==2011 & reelectionist==1
replace year_match=32 if year==2011 & reelectionist==0
replace year_match=34 if year==2012 & reelectionist==1
replace year_match=35 if year==2012 & reelectionist==0

graph twoway (bar mean year_match if reelectionist==1) (bar mean year_match if reelectionist==0)(rcap hipdaf lopdaf year_match if reelectionist==1)

graph export fig1a.pdf,replace

restore


preserve

gen cdf_nt = cdf - cdf_targeted
replace cdf_nt=. if cdf==. | cdf_targeted==.

replace cdf_nt=cdf_nt/1000000

collapse (mean) mean=cdf_nt (sd) sd=cdf_nt (count) n=cdf_nt, by(reelectionist year)
generate hipdaf=mean + invttail(n-1,0.025)*(sd/sqrt(n))
generate lopdaf=mean - invttail(n-1,0.025)*(sd/sqrt(n))

generate year_match=1 if year==2001 & reelectionist==1
replace year_match=2 if year==2001 & reelectionist==0
replace year_match=4 if year==2002 & reelectionist==1
replace year_match=5 if year==2002 & reelectionist==0
replace year_match=7 if year==2003 & reelectionist==1
replace year_match=8 if year==2003 & reelectionist==0
replace year_match=10 if year==2004 & reelectionist==1
replace year_match=11 if year==2004 & reelectionist==0
replace year_match=13 if year==2005 & reelectionist==1
replace year_match=14 if year==2005 & reelectionist==0
replace year_match=16 if year==2006 & reelectionist==1
replace year_match=17 if year==2006 & reelectionist==0
replace year_match=19 if year==2007 & reelectionist==1
replace year_match=20 if year==2007 & reelectionist==0
replace year_match=22 if year==2008 & reelectionist==1
replace year_match=23 if year==2008 & reelectionist==0
replace year_match=25 if year==2009 & reelectionist==1
replace year_match=26 if year==2009 & reelectionist==0
replace year_match=28 if year==2010 & reelectionist==1
replace year_match=29 if year==2010 & reelectionist==0
replace year_match=31 if year==2011 & reelectionist==1
replace year_match=32 if year==2011 & reelectionist==0
replace year_match=34 if year==2012 & reelectionist==1
replace year_match=35 if year==2012 & reelectionist==0

graph twoway (bar mean year_match if reelectionist==1) (bar mean year_match if reelectionist==0)(rcap hipdaf lopdaf year_match if reelectionist==1)
restore

graph export fig1b.pdf,replace


**********************************************
*Table 1: Summary Statistics and Balance Tests
**********************************************

preserve
putexcel set tab1.xls, sheet(sheet1) replace

putexcel A1 = "Variable"
putexcel B1 = "Full Sample"
putexcel C1 = "Non-Reelectionist (NR)"
putexcel D1 = "Reelectionist (R)"
putexcel E1 = "P-values (NR=R)"

putexcel A2 = "Number of observations"
putexcel B2 = "160"
putexcel C2 = "101"
putexcel D2 = "59"

putexcel A4 = "Panel A: Observables"

putexcel A6 = "Female (indicator)"
putexcel A8 = "Years of experience"
putexcel A10 = "Officer (indicator)"
putexcel A12 = "Celebrity (indicator)"
putexcel A14 = "Regional following (indicator)"
putexcel A16 = "Term-ender (indicator)"

putexcel A18 = "Panel B: Outcome Variables"

putexcel A20 = "Total CDF"
putexcel A21 = "Utilization"
putexcel A23 = "Ratio of Targeted to Total"
putexcel A25 = "Targeted to Home Province"
putexcel A27 = "Herfindahl Index of Targeted"

putexcel B6 = "0.15"
putexcel B7 = "(0.36)"
putexcel B8 = "8.34"
putexcel B9 = "(5.41)"
putexcel B10 = "0.17"
putexcel B11 = "(0.38)"
putexcel B12 = "0.24"
putexcel B13 = "(0.43)"
putexcel B14 = "0.30"
putexcel B15 = "(0.46)"
putexcel B16 = "0.11"
putexcel B17 = "(0.31)"
putexcel B21 = "0.54"
putexcel B22 = "(0.36)"
putexcel B23 = "0.65"
putexcel B24 = "(0.35)"
putexcel B25 = "0.19"
putexcel B26 = "(0.28)"
putexcel B27 = "0.27"
putexcel B28 = "(0.27)"

putexcel C6 = "0.14"
putexcel C7 = "(0.35)"
putexcel C8 = "8.78"
putexcel C9 = "(5.99)"
putexcel C10 = "0.15"
putexcel C11 = "(0.36)"
putexcel C12 = "0.26"
putexcel C13 = "(0.44)"
putexcel C14 = "0.30"
putexcel C15 = "(0.46)"
putexcel C16 = "0.17"
putexcel C17 = "(0.38)"
putexcel C21 = "0.48"
putexcel C22 = "(0.34)"
putexcel C23 = "0.68"
putexcel C24 = "(0.36)"
putexcel C25 = "0.21"
putexcel C26 = "(0.29)"
putexcel C27 = "0.26"
putexcel C28 = "(0.26)"

putexcel D6 = "0.17"
putexcel D7 = "(0.39)"
putexcel D8 = "7.59"
putexcel D9 = "(4.19)"
putexcel D10 = "0.20"
putexcel D11 = "(0.41)"
putexcel D12 = "0.20"
putexcel D13 = "(0.41)"
putexcel D14 = "0.31"
putexcel D15 = "(0.46)"
putexcel D16 = "0.00"
putexcel D17 = "(0.00)"
putexcel D21 = "0.63"
putexcel D22 = "(0.39)"
putexcel D23 = "0.59"
putexcel D24 = "(0.34)"
putexcel D25 = "0.16"
putexcel D26 = "(0.26)"
putexcel D27 = "0.29"
putexcel D28 = "(0.28)"

putexcel E6 = "0.686"
putexcel E8 = "0.221"
putexcel E10 = "0.493"
putexcel E12 = "0.538"
putexcel E14 = "0.920"
putexcel E16 = "0.000"
putexcel E21 = "0.005"
putexcel E23 = "0.099"
putexcel E25 = "0.328"
putexcel E27 = "0.539"


**************************************************
*Table 2: Electoral Pressures and Pork Utilization
**************************************************

reg cdf_util reelectionist, vce(cl sen_num)
estimates store reg1
reg cdf_util reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg2
areg cdf_util reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg3
reg pdaf_util reelectionist, vce(cl sen_num)
estimates store reg4
reg pdaf_util reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg5
areg pdaf_util reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg6
reg dpwhca_util reelectionist, vce(cl sen_num)
estimates store reg7
reg dpwhca_util reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg8
areg dpwhca_util reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg9

outreg2 [reg1 reg2 reg3 reg4 reg5 reg6 reg7 reg8 reg9] using  "tab2.xls",replace auto(2) 


**********************************************************************
*Table 3: Electoral Pressures and Ratio of Targeted Pork to Total Pork
**********************************************************************

reg cdf_mix reelectionist, vce(cl sen_num)
estimates store reg1
reg cdf_mix reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg2
areg cdf_mix reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg3
reg pdaf_mix reelectionist, vce(cl sen_num)
estimates store reg4
reg pdaf_mix reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg5
areg pdaf_mix reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg6

outreg2 [reg1 reg2 reg3 reg4 reg5 reg6] using  "tab3.xls",replace auto(2) 


***************************************************************************************
*Table 4: Electoral Pressures and Ratio of Pork to Home Province to Total Targeted Pork
***************************************************************************************

reg cdf_homeprov reelectionist, vce(cl sen_num)
estimates store reg1
reg cdf_homeprov reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg2
areg cdf_homeprov reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg3
reg pdaf_homeprov reelectionist, vce(cl sen_num)
estimates store reg4
reg pdaf_homeprov reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg5
areg pdaf_homeprov reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg6
reg dpwhca_homeprov reelectionist, vce(cl sen_num)
estimates store reg7
reg dpwhca_homeprov reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg8
areg dpwhca_homeprov reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg9

outreg2 [reg1 reg2 reg3 reg4 reg5 reg6 reg7 reg8 reg9] using  "tab4.xls",replace auto(2) 


******************************************************************************
*Table 5: Electoral Pressures and Herfindahl--Hirschman Index of Targeted Pork
******************************************************************************

*Total CDF: What explains concentration of pork across provinces?
reg cdf_hhi reelectionist, vce(cl sen_num)
estimates store reg1
reg cdf_hhi reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg2
areg cdf_hhi reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg3
reg pdaf_hhi reelectionist, vce(cl sen_num)
estimates store reg4
reg pdaf_hhi reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg5
areg pdaf_hhi reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg6
reg dpwhca_hhi reelectionist, vce(cl sen_num)
estimates store reg7
reg dpwhca_hhi reelectionist female officer experience celebrity term_ender, vce(cl sen_num)
estimates store reg8
areg dpwhca_hhi reelectionist female officer experience celebrity term_ender, absorb(year) vce(cl sen_num)
estimates store reg9

outreg2 [reg1 reg2 reg3 reg4 reg5 reg6 reg7 reg8 reg9] using  "tab5.xls",replace auto(2) 


log close
